package com.yalu.util;


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.yalu.entity.data.Products;
import com.yalu.entity.data.ProductsKey;



public class ProductsExcelImportUtil extends BaseExcelImportUtil{
	private List<List<String>> str = null;
	private static final String TITLE = "产品属性,是否专供,代码,产品编号,SKU";
	List<String> CODE = null;
	Map<String,String> codeAttribute = new HashMap<String,String>();
	Map<String,String> codeProductnote = new HashMap<String,String>();
	private static int COLUMN_COUNT = 5;
	
	public ProductsExcelImportUtil() {
		CODE = new ArrayList<String>();
		this.CODE.add("DX");
		this.CODE.add("ZG");
		this.CODE.add("FZG");
		codeAttribute.put("DX", "代销");
		codeAttribute.put("ZG", "自有");
		codeAttribute.put("FZG", "自有");
		
		codeProductnote.put("DX", "");
		codeProductnote.put("ZG", "是电商专供");
		codeProductnote.put("FZG", "非电商专供");
	}

	public List<String> importExcel(String excelFile) {
		try {
			List<List<String>> array = readExcelReturnArray(excelFile);
			return validate(array);
		} catch (Exception e) {
			e.printStackTrace();
			return errMsg;
		}

	}

	private List<String> validate(List<List<String>> array) {
		for (int rowIndex = 0; rowIndex < array.size(); rowIndex++) {
			List<String> row = array.get(rowIndex);
				if (rowIndex == 0) {
					String msg = validateTitle(row);
					if (msg != null) {
						errMsg.add(msg);
					}
				} else {
					String msg = validateData(rowIndex, row);
					if (msg != null) {
						errMsg.add(msg);
					}
				}
		}
		
		for(int i = 0;i<dataList.size();i++){
			Products entity = (Products)dataList.get(i);
			String code = entity.getCode();
			if(!this.CODE.contains(code)){
				String msg = "【代码】栏第【"+(i+1)+"】笔资料有误";
				errMsg.add(msg);
			}else{
				if(!codeAttribute.get(code).equals(entity.getAttribute())){
					String msg = "【代码】栏第【"+(i+1)+"】笔资料有误";
					errMsg.add(msg);
				}else if(!codeProductnote.get(code).equals(entity.getProvidenote())){
					String msg = "【代码】栏第【"+(i+1)+"】笔资料有误";
					errMsg.add(msg);
				}
			}
			for(int j = 0;j<dataList.size();j++){
				if(i!=j){
					Products entityTmp = (Products)dataList.get(j);
					if(entity.getProductsKey().getSku().equals(entityTmp.getProductsKey().getSku())){
						String msg = "【SKU】栏第【"+(i+1)+"】笔"+entity.getProductsKey().getSku()+"与第【"+(j+1)+"】笔"+entityTmp.getProductsKey().getSku()+"，SKU重复";
						errMsg.add(msg);
					}
				}
			}
		}
		return errMsg;
	}

	/**
	 * @Description: 数据格式校验
	 * @param rowIndex
	 * @param row
	 * @return
	 * @date 2013-10-29 上午12:21:12
	 * @author zch
	 */
	private String validateData(int rowIndex, List<String> row) {
		Products data = new Products();
		ProductsKey productsKey = new ProductsKey();
		String value = "";
		for (int columnIndex = 0; columnIndex < COLUMN_COUNT; columnIndex++) {
			value = row.get(columnIndex);
			switch (columnIndex) {
			case 0:
				data.setAttribute(value);break;
			case 1:
				data.setProvidenote(value);
				break;
			case 2:
				data.setCode(value);
				break;
			case 3:
				productsKey.setProduct_id(value);
				break;
			case 4:
				productsKey.setSku(value);
				data.setProductsKey(productsKey);
				break;
			}
		}
		dataList.add(data);
		return null;
	}

	/**
	 * @Description: 校验标题行
	 * @param row
	 * @return
	 * @date 2013-10-29 上午12:19:39
	 * @author zch
	 */
	private String validateTitle(List<String> row) {
		String msg = "";
		String title = "";
		for (int index = 0; index < COLUMN_COUNT; index++) {
			if(index!=COLUMN_COUNT-1){
				title = title+row.get(index) + ",";
			}else{
				title = title+row.get(index) ;
			}
			
		}
		if (!title.equals(TITLE)) {
			msg = "首行标题依次应为--" + TITLE;
			return msg;
		} else {
			return null;
		}
	}

	public List<List<String>> readExcelReturnArray(String excelFile)
			throws Exception {
		List<List<String>> array = new ArrayList<List<String>>();
		InputStream stream = null;
		try {
			stream = new FileInputStream(excelFile);
			XSSFWorkbook myWorkBook = new XSSFWorkbook(stream);
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
			Iterator<Row> rowIter = mySheet.rowIterator();
			String cellValue = "";
			int rowIndex = 0;
			while (rowIter.hasNext()) {
				XSSFRow myRow = (XSSFRow) rowIter.next();
				if(myRow==null){
					continue;
				}
				List<String> rowArr = new ArrayList<String>();
				for (int cellNum = 0; cellNum <= COLUMN_COUNT; cellNum++) {
					cellValue = "";
					XSSFCell myCell = (XSSFCell) myRow.getCell(cellNum);
					if(myCell==null){
						rowArr.add(cellValue.trim());
						continue;
					}
//					int columnIndex = myCell.getColumnIndex();
					
					switch (myCell.getCellType()) {
					case XSSFCell.CELL_TYPE_NUMERIC:
						double numCellValue = myCell.getNumericCellValue();
						if (DateUtil.isCellDateFormatted(myCell)) {
							Date t = myCell.getDateCellValue();
							SimpleDateFormat format = new SimpleDateFormat(
									"yyyy/MM/dd");
							cellValue = format.format(t);
						} else {
							cellValue = numCellValue + "";
						}
						break;
					case XSSFCell.CELL_TYPE_STRING:
						String strCellString = myCell.getStringCellValue();
						cellValue = strCellString;
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						break;
					}
					rowArr.add(cellValue.trim());
				}
				for (int columnIndex = COLUMN_COUNT; columnIndex > rowArr.size();) {
					rowArr.add("");
				}
				//不加入空行数据
				for(String str : rowArr){
					if(!str.equals("")){
						array.add(rowArr);
						break;
					}
				}
				rowIndex++;
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw e;
		} catch (IOException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (stream != null) {
				try {
					stream.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
			}
		}

		return array;
	}

	public List<List<String>> getStr() {
		return str;
	}

	public void setStr(List<List<String>> str) {
		this.str = str;
	}

}
